package com.example.oa;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.xml.transform.Result;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

@WebServlet("/employee")
public class EmployeeServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req, resp);

    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String action = req.getParameter("action");
        if (action.equals("sign_up")) {
            signUp(req, resp);
        }
        if (action.equals("sign_in")) {
            signIn(req, resp);
        }
        if (action.equals("add")) {
            add(req, resp);
        }
//        先查询再更新
        if (action.equals("findById")) {
            System.out.println("if");
            findById(req, resp);
        }
        if (action.equals("update")) {
            update(req, resp);
        }
        //注销
        if (action.equals("sign_out")) {
            signOut(req, resp);
        }
        if (action.equals("removeById")) {
            removeById(req, resp);
        }
        if (action.equals("detail")) {
            detail(req, resp);
        }
    }

    protected void detail(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        int id = Integer.parseInt(req.getParameter("id"));

        Connection connection = DB.getConnection();
        String sql = "select * from db_oa.employee where id = ?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();

            resultSet.next();

            String name = resultSet.getString("name");
            String password = resultSet.getString("password");
            int age = resultSet.getInt("age");
            String gender = resultSet.getString("gender");
            int role = resultSet.getInt("role");

            Employee employee = new Employee(id, name, password, age, gender, role);

            req.getSession().setAttribute("employee", employee);

            resp.sendRedirect("admin/detail.jsp");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(resultSet, preparedStatement, connection);
        }
    }

    protected void removeById(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        int id = Integer.parseInt(req.getParameter("id"));

        Connection connection = DB.getConnection();
        String sql = "delete from db_oa.employee where  id = ?";

        PreparedStatement preparedStatement = null;


        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            preparedStatement.executeUpdate();

            req.getSession().setAttribute("employees", getAllEmployees());
            resp.sendRedirect("admin/admin.jsp");

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(null, preparedStatement, connection);
        }
    }

    protected void signOut(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.getSession().invalidate();
        resp.sendRedirect("index.jsp");
    }

    protected void update(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        int id = Integer.parseInt(req.getParameter("id"));
        String name = req.getParameter("name");
        int age = Integer.parseInt(req.getParameter("age"));

        Connection connection = DB.getConnection();
        String sql = "update db_oa.employee set name = ?, age = ? where id = ?";
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            preparedStatement.setInt(2, age);
            preparedStatement.setInt(3, id);

            preparedStatement.executeUpdate();

            req.getSession().setAttribute("employees", getAllEmployees());
            resp.sendRedirect("admin/admin.jsp");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(null, preparedStatement, connection);
        }
    }

    protected void findById(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("find");
        int id = Integer.parseInt(req.getParameter("id"));
        Connection connection = DB.getConnection();
        String sql = "select * from db_oa.employee where id = ?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            resultSet.next();

            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            Employee employee = new Employee(id, name, age);

            System.out.println(employee);
            req.getSession().setAttribute("employee", employee);
            resp.sendRedirect("admin/edit.jsp");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(resultSet, preparedStatement, connection);
        }
    }

    protected void add(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String name = req.getParameter("name");
        String password = req.getParameter("password");
        int age = Integer.parseInt(req.getParameter("age"));
        String gender = req.getParameter("gender");


        Connection connection = DB.getConnection();
        String sql = "insert into db_oa.employee(name,password,age,gender)values(?,?,?,?)";

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;


        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            preparedStatement.setString(2, password);
            preparedStatement.setInt(3, age);
            preparedStatement.setString(4, gender);


            preparedStatement.executeUpdate();


            req.getSession().setAttribute("employees", getAllEmployees());
            resp.sendRedirect("admin/admin.jsp");


        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(resultSet, preparedStatement, connection);
        }
    }


    private ArrayList<Employee> getAllEmployees() {
        Connection connection = DB.getConnection();
        //  新的查询语句 查询所有用户的列表
        String sqlSelect = "select * from db_oa.employee where role<>1";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //管理员登陆 显示用户列表
            preparedStatement = connection.prepareStatement(sqlSelect);
            resultSet = preparedStatement.executeQuery();
            //容器=========================================================
            ArrayList<Employee> employees = new ArrayList<>();

            while (resultSet.next()) {
                int idDB = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String gender = resultSet.getString("gender");
                Employee employee = new Employee(idDB, name, age, gender);
                employees.add(employee);
            }
            //=============================================================
            return employees;


        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(resultSet, preparedStatement, connection);
        }


    }

    // 登录
    protected void signIn(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        int id = Integer.parseInt(req.getParameter("id"));
        String password = req.getParameter("password");

        Connection connection = DB.getConnection();
        String sql = "select * from db_oa.employee where id = ? and password = ?";


        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            preparedStatement.setString(2, password);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                int role = resultSet.getInt("role");

                // 两种角色登录后不能访问对方的主页
                req.getSession().setAttribute("role", role);

                if (role == 0) {
                    //用户登录：
                    //id 已经有了
                    String name = resultSet.getString("name");
                    int age = resultSet.getInt("age");
                    String gender = resultSet.getString("gender");
                    //定义一个实体
                    Employee employee = new Employee(id, name, age, gender);
                    req.getSession().setAttribute("name", name);
                    req.getSession().setAttribute("role", 0);
                    req.getSession().setAttribute("employee", employee);
                    //跳转页面
                    resp.sendRedirect("home.jsp");


                }
                if (role == 1) {
                    String name = resultSet.getString("name");
                    req.getSession().setAttribute("name", name);
                    req.getSession().setAttribute("role", 1);
                    req.getSession().setAttribute("employees", getAllEmployees());
                    // "admin/admin.jsp"  放在同一个文件里面 方便管理员操作 添加编辑删除
                    resp.sendRedirect("admin/admin.jsp");
                }
                return;
            }
            req.setAttribute("error", "Invalid id or password.");
            req.getRequestDispatcher("index.jsp").forward(req, resp);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(resultSet, preparedStatement, connection);
        }
    }

    private void signUp(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        String name = req.getParameter("name");
        String password = req.getParameter("password");
        int age = Integer.parseInt(req.getParameter("age"));
        String gender = req.getParameter("gender");


        Connection connection = DB.getConnection();
        String sql = "insert into db_oa.employee(name,password,age,gender)values(?,?,?,?)";

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;


        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            preparedStatement.setString(2, password);
            preparedStatement.setInt(3, age);
            preparedStatement.setString(4, gender);
            preparedStatement.executeUpdate();
            // 注册后 用户要靠ID登录 将id返回给用户

            preparedStatement = connection.prepareStatement("select last_insert_id()");
            resultSet = preparedStatement.executeQuery();
            // 查询最后一次id 并返回 获取 此值
            resultSet.next();
            //起到记录的作用
            int id = resultSet.getInt(1);


            // getSession 是会话框 可以一直显示此对话
            //req.setAttribute("message","Sign Up ...");
            //req.getRequestDispatcher("index.jsp").forward(req,resp);
            req.getSession().setAttribute("message", "Sign Up Successful,your id is : " + id);
            resp.sendRedirect("index.jsp");


        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(resultSet, preparedStatement, connection);
        }
    }


}
